﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupSnapshotDrop]
#-- Purpose:		Drops one or more snapshots of a database.  Purpose is to 
#--					be used in a night batch process to remove periodic snapshots 
#--					that were created throughout the night
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupSnapshotDrop]
(
	@database_name			sysname,
	@label					sysname			= NULL,
	@keep_snap_db_name		sysname			= NULL
)
AS

SET NOCOUNT ON 

--- Validate Parameters
IF ISNULL(@database_name, '') = ''
  BEGIN
	RAISERROR ('The parameter @database_name is required to drop database snapshot(s)', 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE		@snap_db_name sysname, @sSQL nvarchar(MAX), @crs_db_name sysname, @crs_snap_name sysname,
			@i int
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupSnapshotDrop'

--- Initialize Local Variables
SELECT		@database_name = LTRIM(RTRIM(@database_name)),
			@label = LTRIM(RTRIM(@label)),
			@snap_db_name = 'SNAP_' + @database_name + '_' + @label,
			@keep_snap_db_name = LTRIM(RTRIM(ISNULL(@keep_snap_db_name, '')))

--- @snap_db_name and @keep_snap_db_name can't be the same
IF @snap_db_name = @keep_snap_db_name AND @keep_snap_db_name <> ''
  BEGIN
	RAISERROR ('The parameters @database_name/@label and @keep_snap_db_name equal the same snapshot.', 16, 1)
	RETURN
  END

--- Drop the snapshot(s)
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT	d1.name database_name,
			d2.name snapshot_name
	FROM	master.sys.databases d1
	JOIN	master.sys.databases d2 ON d2.source_database_id = d1.database_id
	WHERE	--d1.name = @database_name		-- ** temporary workaround to force the Silo snapshot to delete
			--AND							-- ** even if the base database has been renamed while a snapshot is on it
			(
				d2.name = @snap_db_name
				OR
				@snap_db_name IS NULL
			)
			AND d2.name <> @keep_snap_db_name
OPEN crs
FETCH NEXT FROM crs INTO  @crs_db_name, @crs_snap_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Drop the snapshot
	SELECT	@sSQL = 'DROP DATABASE ' + @crs_snap_name
	EXEC	(@sSQL)

	--- Create a log entry that a snapshot was dropped
	EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
	
	FETCH NEXT FROM crs INTO  @crs_db_name, @crs_snap_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF
